package com.ptswitch.material.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;

import com.hxzon.util.AntZipUtil;
import com.hxzon.util.OgnlUtil;
import com.ptswitch.material.model.Bom;
import com.ptswitch.material.model.BomMaterial;
import com.ptswitch.material.model.Material;
import com.ptswitch.material.model.Upload;
import com.ptswitch.material.service.ExcelService;
import com.ptswitch.material.util.ColumnMap;

@Service("excelSer")
public class ExcelServiceImpl extends LoggerService implements ExcelService {

	private static ColumnMap columnMap = new ColumnMap();

	private Workbook wb;
	private Sheet sheet;

	public void read(String parentpath, String subpath) {
		String filePath = parentpath + subpath;
		logger.debug(filePath);
		wb = null;// must reset,or will wrong
		InputStream ins = null;
		try {
			ins = new FileInputStream(filePath);
			wb = WorkbookFactory.create(ins);
		} catch (Exception e) {// InvalidFormatException,IOException,RuntimeException
			logger.error(e.getMessage());
			throw new RuntimeException(e.getMessage());
		}finally{
			try {
				ins.close();
			} catch (IOException e) {
				logger.error(e.getMessage());
			}
			
		}
		
	}

	public void write(String absoluteFilePath) {
		try {
			OutputStream ous = FileUtils.openOutputStream(new File(absoluteFilePath));
			wb.write(ous);
			ous.close();
		} catch (IOException e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	public void buildBomFromExcel(Bom bom) {
		if (wb == null) {
			return ;
		}
		int sheetNum = wb.getNumberOfSheets();
		List<BomMaterial> bomMaterials=new ArrayList<BomMaterial>();
		BomMaterial bomMaterial = null;
		for (int j = 0; j < sheetNum; j++) {
			sheet = wb.getSheetAt(j);
			if (sheet == null) {
				continue;
			}
			buildColumnMap(sheet, columnMap);
			if (columnMap.get("name") != -1) {
				throw new RuntimeException("找到‘物料名称’列，请确认导入的是Bom表，而不是物料表");
			}
			Row row = null;
			int lastrow = sheet.getLastRowNum();
			for (int i = 1; i <= lastrow; i++) {
				row = sheet.getRow(i);
				if (isEmptyRow(row)) {
					continue;
				}
				bomMaterial = new BomMaterial();
				rowToBomMaterial(row, bomMaterial);
				bomMaterial.setBom(bom);
				bomMaterials.add(bomMaterial);
			}
		}
		bom.setBomMaterials(bomMaterials);
	}

	public void buildMaterialsFromExcel(List<Material> materials) {
		if (wb == null) {
			return ;
		}
		int sheetNum = wb.getNumberOfSheets();
		for (int j = 0; j < sheetNum; j++) {
			sheet = wb.getSheetAt(j);
			if (sheet == null) {
				continue;
			}
			buildColumnMap(sheet, columnMap);
			if (columnMap.get("name") == -1) {
				throw new RuntimeException("找不到‘物料名称’列，请确认导入的是物料表，而不是Bom表");
			}
			Row row = null;
			Material material = null;
			String code = null;
			int lastrow = sheet.getLastRowNum();
			for (int i = 1; i <= lastrow; i++) {
				row = sheet.getRow(i);
				code = getCode(row);
				if (code.isEmpty()) {// fixme not code don't add
					logger.debug("row {} is no fine code", i);
					continue;
				}
				material = new Material();
				rowToMaterial(row, material);
				materials.add(material);
			}
		}
	}

	public void buildDataFromZip(List<Material> materials,List<Bom> boms,String parentPath, String zipFilePath) {// parentPath=all/upload
		FileUtils.deleteQuietly(new File(parentPath+"importZip"));
		try {
			FileUtils.forceMkdir(new File(parentPath+"importZip"));
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
		AntZipUtil.unzip(parentPath + "importZip", parentPath + zipFilePath);
		read(parentPath, "importZip/materials.xls");
		buildMaterialsFromExcel(materials);
		read(parentPath,"importZip/boms.xls");
		sheet = wb.getSheetAt(0);
		int lastrow = sheet.getLastRowNum();
		for (int i = 1; i <= lastrow; i++) {
			Bom bom=new Bom();
			Row row=sheet.getRow(i);
			String fileName = getCellString(row, 3);
			String filePath = "importZip/"+getCellString(row, 3);
			bom.setCode(getCellString(row, 1));
			bom.setVersion(getCellString(row, 2));
			bom.setFileName(filePath);
			Upload upload=new Upload();
			upload.setFileName(fileName);
			upload.setFilePath(filePath);
			upload.setFileType("application/vnd.ms-excel");
			bom.setUpload(upload);
			boms.add(bom);
		}
		for (Bom bom : boms) {
			read(parentPath, bom.getFileName());
			buildBomFromExcel(bom);
		}
	}

	public void outputDataToZip(List<Material> materials,List<Bom> boms,String parentPath, String outputFile) {
		try {
			FileUtils.deleteDirectory(new File(parentPath + "/tmpzip"));
			FileUtils.forceMkdir(new File(parentPath + "/tmpzip"));
		} catch (IOException e) {
			logger.debug(e.getMessage());
		}
		outputMaterials(materials);
		write(parentPath + "/tmpzip/materials.xls");
		outputBomList(boms,parentPath);
		write(parentPath + "/tmpzip/boms.xls");
		AntZipUtil.zip(parentPath + outputFile, parentPath + "/tmpzip");
	}

	//
	public void outputMaterials(List<Material> materials) {
		wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet();
		Row row = null;
		// title
		row = sheet.createRow(0);
		setCellString(row, 0, "物料编号");
		setCellString(row, 1, "物料名称");
		setCellString(row, 2, "规格型号");
		setCellString(row, 3, "器件封装");
		setCellString(row, 4, "品牌");
		setCellString(row, 5, "供货商");
		setCellString(row, 6, "器件描述");
		setCellString(row, 7, "PCB封装");
		setCellString(row, 8, "临时编码");
		setCellString(row, 9, "采购状态");
		setCellString(row, 10, "备注");
		// rows
		int i = 1;
		for (Material material : materials) {
			row = sheet.createRow(i);
			materialToRow(row, material);
			i++;
		}
	}

	public void outputBom(Bom bom) {
		wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet();
		Row row = null;
		// title
		row = sheet.createRow(0);
		setCellString(row, 0, "code");
		setCellString(row, 1, "comment");
		setCellString(row, 2, "designator");
		setCellString(row, 3, "footprint");
		setCellString(row, 4, "voltage");
		setCellString(row, 5, "value");
		setCellString(row, 6, "quantity");
		setCellString(row, 7, "stuffOpt");
		setCellString(row, 8, "警告");
		// bom materials
		int i = 1;
		for (BomMaterial bomMaterial : bom.getBomMaterials()) {
			row = sheet.createRow(i);
			bomMaterialToRow(row, bomMaterial);
			i++;
		}
	}

	public void outputBomList(List<Bom> boms, String parentPath) {
		wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet();
		Row row = null;
		int i = 1;
		for (Bom bom : boms) {
			try {
				row = sheet.createRow(i);
				setCellString(row, 1, bom.getCode());
				setCellString(row, 2, bom.getVersion());
				setCellString(row, 3, bom.getFileName());
				setCellString(row, 4, bom.getRemark());
				i++;
				FileUtils.copyFile(new File(parentPath + bom.getFileName()), new File(parentPath + "/tmpzip/"
						+ bom.getFileName()));
			} catch (IOException e) {
				logger.debug(e.getMessage());
			}
	
		}
	}

	private static String getCode(Row row) {
		return getCellString(row, "code");
	}

	private static Material rowToMaterial(Row row, Material material) {
		for (String key : OgnlUtil.getAllStringProperty(Material.class)) {
			OgnlUtil.setValue(material, key, getCellString(row, key));
		}
		String state = material.getState();
		if (state == null || state.isEmpty()) {
			state = "一般";
		}
		if (state.equals("推荐") || state.equals("一般") || state.equals("不推荐") || state.equals("停用")) {

		} else {
			state = state + "描述不规范，请检查";
		}
		material.setState(state);
		return material;
	}

	private static BomMaterial rowToBomMaterial(Row row, BomMaterial bomMaterial) {
		for (String key : OgnlUtil.getAllStringProperty(BomMaterial.class)) {
			OgnlUtil.setValue(bomMaterial, key, getCellString(row, key));
		}
		return bomMaterial;
	}

	private static void materialToRow(Row row, Material material) {
		setCellString(row, 0, material.getCode());
		setCellString(row, 1, material.getName());
		setCellString(row, 2, material.getSpecification());
		setCellString(row, 3, material.getFootprint());
		setCellString(row, 4, material.getBrand());
		setCellString(row, 5, material.getSupplier());
		setCellString(row, 6, material.getDescription());
		setCellString(row, 7, material.getPcbDecal());
		setCellString(row, 8, material.getTmpCode());
		setCellString(row, 9, material.getState());
		setCellString(row, 10, material.getRemark());
	}

	private void bomMaterialToRow(Row row, BomMaterial bomMaterial) {
		setCellString(row, 0, bomMaterial.getCode());
		setCellString(row, 1, bomMaterial.getComment());
		setCellString(row, 2, bomMaterial.getDesignator());
		setCellString(row, 3, bomMaterial.getFootprint());
		setCellString(row, 4, bomMaterial.getVoltage());
		setCellString(row, 5, bomMaterial.getValue());
		setCellString(row, 6, bomMaterial.getQuantity());
		setCellString(row, 7, bomMaterial.getStuffOpt());
		String error = "";
		if (bomMaterial.getCode().isEmpty()) {
			error = "编码为空";
		} else if (bomMaterial.getMaterial() == null) {
			error = "对应物料不存在";
		} else if (bomMaterial.getMaterial().getState().contains("未发布")) {
			error = "物料未发布";
		} else if (bomMaterial.getMaterial().getState().contains("不推荐")) {
			error = "物料不推荐";
		} else if (bomMaterial.getMaterial().getState().contains("停用")) {
			error = "物料停用";
		} else if(!bomMaterial.getMaterial().getPcbDecal().equals(bomMaterial.getFootprint())){
			error = "footprint与物料PCB封装不一致";
		}
		CellStyle style = null;
		if (!error.isEmpty()) {
			style = wb.createCellStyle();
			style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
			style.setFillBackgroundColor(IndexedColors.RED.getIndex());
			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		}
		setCellString(row, 8, error, style);
	}

	public static void setCellString(Row row, int colIndex, String value) {
		setCellString(row, colIndex, value, null);
	}

	public static void setCellString(Row row, int colIndex, String value, CellStyle cellStyle) {
		Cell cell = row.createCell(colIndex);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		if (cellStyle != null) {
			cell.setCellStyle(cellStyle);
		}
	}

	public static String getCellString(Sheet sheet, int rowIndex, int colIndex) {
		if (sheet == null) {
			return "";
		}
		return getCellString(sheet.getRow(rowIndex), colIndex);

	}

	public static String getCellString(Row row, String columnName) {
		return getCellString(row, columnMap.get(columnName));
	}

	public static String getCellString(Row row, int colIndex) {
		if (row == null) {
			return "";
		}
		return getCellString(row.getCell(colIndex));
	}

	public static String getCellString(Cell cell) {
		String value = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				value = String.valueOf(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:// not escape
				value = cell.getStringCellValue().trim();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_BLANK:
			}
		}
		return value;

	}

	public static boolean isEmptyRow(Row row) {
		for (int i = 0; i < 5; i++) {
			if (!getCellString(row, i).isEmpty()) {
				return false;
			}
		}
		return true;
	}

	public static void buildColumnMap(Sheet sheet, ColumnMap columnMap) {
		columnMap.clear();
		Row row = sheet.getRow(0);
		int colNum = row.getLastCellNum();
		String value = null;
		for (int i = 0; i < colNum; i++) {
			value = getCellString(sheet, 0, i);
			if (!value.isEmpty()) {
				columnMap.put(value, i);
				// logger.debug(value + ":" + i);
			}
		}
	}

}
